
import xlrd                              #打开解释器，打开文档
open_book=xlrd.open_workbook(r"D:\练习Python\百度合作单位-人员管理-二期.xls")#打开文件所在位置，)#r:读。r+:读不到就在新建一个。w:写入。
                                         # # 打开文档的位置＋工作表名称后缀.xls

sheet=open_book.sheet_by_index(0)
print(sheet.nrows)     #获取行数
print(sheet.ncols)      #获取列数

#   ##获取整个列表每列的内容
# for  i in range(sheet.nrows):
#     print(sheet.row_values(i))          #row:竖取，col:横取
# ###for i in range(sheet.ncols):
# ###    print(sheet.row_values(i))            #row:竖取，col:横取
# for a in range(sheet.ncols):
#     if sheet.col_values(a)[0]=="真实姓名":
#         print(sheet.col_values(a))

# 统计所有表格中的人数
        # print(sheet.col_values(a)[1:])    #从角标1开始打印到最后
print(sheet.nrows-1,"人")

#####统计电信(14,17)，联通(15)，移动(13) 用户数量
list_dianx=[]
list_liant=[]
list_yid=[]

for b in range(sheet.ncols):
    if sheet.col_values(b)[0]=="电话号码":
        telephone_number=sheet.col_values(b)[1:]
        for c in telephone_number:
            if c[0:2]=="14"or c[0:2]=="17":
                list_dianx.append(c)
            elif c[0:2] =="15":
                list_liant.append(c)
            elif c[0:2] == "13":
                list_yid.append(c)
print(" 电信用户数量：",len(list_dianx),"人\n","联通用户数量：",len(list_liant),"人\n","移动用户数量：",len(list_yid),"人\n",)

men=[]
women=[]
for b in range(sheet.ncols):
    if sheet.col_values(b)[0]=="性别":
        sex_name=sheet.col_values(b)[1:]
        for c in sex_name:
            if c=="男":
                men.append(c)
            elif c=="女":
                women.append(c)
print(" 男：",len(men),"人\n","女：",len(women),"人\n")


old=[]
for b in range(sheet.ncols):
    if sheet.col_values(b)[0] == "年龄":
        age=sheet.col_values(b)[1:]
        for c in age:
            if c > 45 :
                old.append(c)
print(" 45岁以上老员工：", len(old), "人")

high_salaries=[]
low_pay=[]
for b in range(sheet.ncols):
    if sheet.col_values(b)[0] == "薪资":
        salary=sheet.col_values(b)[1:]
        for c in salary:
            if c > 8000 :
                high_salaries.append(c)
            elif c < 3000 :
                low_pay.append(c)
print(" 高薪人员：", len(high_salaries), "人\n", "低薪人员：", len(low_pay), "人")

Media_companies=[]
for b in range(sheet.ncols):
    if sheet.col_values(b)[0] == "外包公司":
         the_company=sheet.col_values(b)[1:]
         for c in the_company:
             if "传媒" in c :
                 Media_companies.append(c)
print(" 就业传媒公司的员工：", len(Media_companies), "人")

Area_of_high_risk=[]
for b in range(sheet.ncols):
    if sheet.col_values(b)[0] == "居住地址":
         address=sheet.col_values(b)[1:]
         for j in address:
             if "黑龙江" in j :
                 Area_of_high_risk.append(j)
             elif "北京" in j :
                 Area_of_high_risk.append(j)
             elif "福建" in j :
                 Area_of_high_risk.append(j)
             elif "四川" in j :
                 Area_of_high_risk.append(j)
print(" 高危地区人数：", len(Area_of_high_risk), "人")



